﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using KingSite.SyncDB.Core.Entity;
using KingSite.SyncDB.Core.Repository;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Collections;
using System.Reflection;

[assembly: log4net.Config.XmlConfigurator(Watch = true)]
namespace KingSite.SyncDB.Repository {
    public class DataAccessFactory {
        public static AbstractDataAccess GetDataAccess(SyncJobInfo job, IList<SyncEntity> syncList) {
            if (job.DDbTypeId == (int)DBTypeEnum.SQLSERVER) {
                return new MSSqlDataAccess(job, syncList);
            }
            //else if (type == DBTypeEnum.MYSQL) {
            //    return new MySqlDataAccess();
            //}
            //else if (type == DBTypeEnum.ORACLE) {
            //    return new OracleDataAccess();
            //}
            else return new MSSqlDataAccess(job, syncList);
        }
    }

    public abstract class AbstractDataAccess {
        const string TIMESTAMP = "timestamp";
        public int PageSize { get { return 100; } }
        public SyncJobInfo job;
        public IList<SyncEntity> syncList;
        public DataSet sourceTable;

        public List<string> primaryKeys = new List<string>();
        public List<Hashtable> primaryKeyValues;
        public List<Hashtable> updatePrimaryKeyValues;
        public List<Hashtable> insertPrimaryKeyValues;

        TBRepository tbr = new TBRepository();
        //创建日志记录组件实例
        public log4net.ILog log = log4net.LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);

        public AbstractDataAccess(SyncJobInfo job, IList<SyncEntity> syncList) {
            this.job = job;
            this.syncList = syncList;
            primaryKeys = GetPrimaryKeys();            
        }

        public abstract SyncJobDetailInfo GetSyncJobDetailInfo();
        /// <summary>
        /// 获得源表的所有时间戳更新的记录行
        /// </summary>
        /// <param name="job"></param>
        /// <param name="list"></param>
        /// <returns></returns>
        public abstract DataSet GetSourceTable(string lastTimeStamp, int startRow, int endRow);
        /// <summary>
        /// 获得所有UpdateSql与参数
        /// </summary>
        /// <param name="job"></param>
        /// <param name="sourceTable"></param>
        /// <param name="list"></param>
        /// <returns></returns>
        public abstract List<ExecSqlObject> GetUpdateSql();
        /// <summary>
        /// 获得所有InsertSql与参数
        /// </summary>
        /// <param name="job"></param>
        /// <param name="sourceTable"></param>
        /// <param name="list"></param>
        /// <returns></returns>
        public abstract List<ExecSqlObject> GetInsertSql();
        /// <summary>
        /// 执行需要同步的Sql
        /// </summary>
        /// <param name="job"></param>
        /// <param name="detail"></param>
        public abstract void ExecSyncSql(SyncJobDetailInfo detail);
        /// <summary>
        /// 获得表的最大时间戳记录值
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public abstract string GetTableMaxTimeStamp(string dbConnstring, string tableName);
        /// <summary>
        /// 根据源数据库的时间戳更新Sqlite
        /// </summary>
        /// <param name="job"></param>
        /// <param name="so"></param>
        public void UpdateRowTimeStamp(ExecSqlObject so) {
            tbr.UpdateRowTimeStamp(job.StId, so.RowTimeStamp);
        }
        /// <summary>
        /// 记录日志
        /// </summary>
        /// <param name="job"></param>
        /// <param name="so"></param>
        /// <param name="type"></param>
        public void AddLog(ExecSqlObject so, string type) {
            StringBuilder sb = new StringBuilder();
            sb.Append(string.Format("{0} {1}", job.JobName, type));
            foreach (DictionaryEntry kv in so.KeyValues) {
                sb.Append(string.Format(" {0}={1} and ", kv.Key, kv.Value));
            }
            sb.Remove(sb.Length - 5, 5); //移除最后一个 and
            string msg = sb.ToString();
            log.Info(msg);
        }
        /// <summary>
        /// 获得当前行的时间戳
        /// </summary>
        /// <param name="sourceTable"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public object GetRowTimeStamp(Hashtable ht) {
            return GetSourceColumnValue(ht, TIMESTAMP);
        }
        /// <summary>
        /// 返回源列值
        /// </summary>
        /// <param name="sourceTable"></param>
        /// <param name="id"></param>
        /// <param name="columnName"></param>
        /// <returns></returns>
        public object GetSourceColumnValue(Hashtable ht, string columnName) {
            object result = null;
            StringBuilder sb = new StringBuilder();
            foreach (DictionaryEntry kv in ht) {
                if (kv.Value.GetType().Equals(typeof(string))) {
                    sb.Append(string.Format("{0}='{1}'", kv.Key, kv.Value));
                }
                else {
                    sb.Append(string.Format("{0}={1}", kv.Key, kv.Value));
                }
                sb.Append(" and ");
            }
            sb.Remove(sb.Length - 5, 5); //移除最后一个 and
            string filter = sb.ToString();
            DataRow[] rows = sourceTable.Tables[0].Select(filter);
            if (rows.Length > 0)
                result = rows[0][columnName];
            return result;
        }
        /// <summary>
        /// 获得所有时间戳变化记录行的 主键与主键值
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        public List<Hashtable> GetPrimaryKeyValues() {
            primaryKeyValues = new List<Hashtable>();
            foreach (DataRow dr in sourceTable.Tables[0].Rows) {
                Hashtable ht = new Hashtable();
                foreach (string key in primaryKeys) {
                    object value = dr[key];
                    ht.Add(key, value);
                }
                primaryKeyValues.Add(ht);
            }
            return primaryKeyValues;
        }

        public List<Hashtable> GetInsertPrimaryKeyValues() {

            if (updatePrimaryKeyValues != null) {
                List<Hashtable> result = new List<Hashtable>();
                //在所有的时间戳变化的记录行里面，把update的排除掉，剩下的都是insert
                foreach (Hashtable pk in primaryKeyValues) {
                    if (!IsInUpdatePrimaryKeyValues(pk)) {
                        result.Add(pk);
                    }
                }
                return result;
            }
            else {
                return primaryKeyValues;
            }
        }

        private bool IsInUpdatePrimaryKeyValues(Hashtable pk) {
            bool b = false;
            foreach (Hashtable updateKeyValues in updatePrimaryKeyValues) {
                if (CompareHashtable(pk, updateKeyValues)) {
                    b = true;
                    break;
                }
            }
            return b;
        }

        private bool CompareHashtable(Hashtable ht1, Hashtable ht2) {
            bool b = true;
            foreach (string key in ht1.Keys) {
                if (!ht2.ContainsKey(key)) {
                    b = false;
                    break;
                }
                else {
                    if (Convert.ToString(ht1[key]) != Convert.ToString(ht2[key])) {
                        b = false;
                        break;
                    }
                }
            }
            return b;
        }


        private DataSet GetUpdateKeyValueDataSet() {
            DataSet ds = null;
            if (primaryKeyValues.Count > 0) {
                StringBuilder sb = new StringBuilder();
                sb.Append("select ");
                foreach (string key in primaryKeys) {
                    sb.Append(key);
                    sb.Append(", ");
                }
                sb.Remove(sb.Length - 2, 2); //移除最后一个,
                sb.Append(" from ");
                sb.Append(job.DTableName);
                sb.Append(" where ");
                foreach (Hashtable ht in primaryKeyValues) {
                    sb.Append(" ( ");
                    foreach (DictionaryEntry kv in ht) {
                        sb.Append(kv.Key);
                        sb.Append(" = ");
                        if (kv.Value.GetType() == typeof(string)) { //如果是字符串需要加引号
                            sb.Append("'");
                        }
                        sb.Append(kv.Value);
                        if (kv.Value.GetType() == typeof(string)) { //如果是字符串需要加引号
                            sb.Append("'");
                        }
                        sb.Append(" and ");
                    }
                    sb.Remove(sb.Length - 5, 5); //移除最后一个 and
                    sb.Append(" )");
                    sb.Append(" or ");
                }
                sb.Remove(sb.Length - 4, 4); //移除最后一个 or
                string sql = sb.ToString();
                //这里要连接目标数据库查询
                try {
                    SqlDatabase sdb = new SqlDatabase(job.DDbconnstring);
                    ds = sdb.ExecuteDataSet(CommandType.Text, sql);
                }
                catch (Exception ex) {
                    log.Info(string.Format("任务{0}发生错误：连接目标数据库失败! {1}", job.JobName, ex.Message));
                }
            }
            return ds;
        }

        public List<Hashtable> GetUpdatePrimaryKeyValues() {
            List<Hashtable> result = null;
            DataSet ds = GetUpdateKeyValueDataSet();
            if (ds != null && ds.Tables[0].Rows.Count > 0) {
                result = new List<Hashtable>();
                foreach (DataRow dr in ds.Tables[0].Rows) {
                    Hashtable ht = new Hashtable();
                    foreach (string key in primaryKeys) {
                        object value = dr[key];
                        ht.Add(key, value);
                    }
                    result.Add(ht);
                }
            }
            return result;
        }

        protected List<string> GetPrimaryKeys() {
            List<string> keys = new List<string>();
            foreach (SyncEntity entity in syncList) {
                if (entity.SCIsPrimaryKey.Equals(true)) {
                    keys.Add(entity.SColumnName);
                }
            }
            return keys;
        }

        public abstract int GetTotalRecords();
        /// <summary>
        /// 执行同步任务
        /// </summary>
        public virtual void ExecSyncJob() {
            int totalRecords = GetTotalRecords();
            int pageIndex, pageCount, startRow, endRow;
            pageIndex = 1;
            pageCount = 1;
            string lastTimeStamp = job.SLastTimeStamp;
            if (totalRecords > 0) {
                while (pageIndex <= pageCount) {
                    CPage(pageIndex, this.PageSize, totalRecords, out startRow, out endRow, out pageCount);
                    sourceTable = GetSourceTable(lastTimeStamp, startRow, endRow);
                    if (sourceTable != null && sourceTable.Tables[0].Rows.Count > 0) {
                        primaryKeyValues = GetPrimaryKeyValues();
                        updatePrimaryKeyValues = GetUpdatePrimaryKeyValues();
                        insertPrimaryKeyValues = GetInsertPrimaryKeyValues();
                        SyncJobDetailInfo detail = GetSyncJobDetailInfo();
                        ExecSyncSql(detail);
                        pageIndex++;
                    }
                }
            }
        }

        public void CPage(int pageIndex, int pageSize, int recordCount, out int startRow, out int endRow, out int pageCount) {
            pageCount = (int)Math.Ceiling(recordCount / (double)pageSize);
            pageIndex = pageIndex > pageCount ? pageCount : pageIndex;
            pageIndex = pageIndex <= 0 ? 1 : pageIndex;
            startRow = (pageIndex - 1) * pageSize + 1;
            endRow = pageIndex * pageSize;
        }

    }




}
